﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 链接表操作类(初步审核通过)
    /// 抽象类不允许实例化
    /// </summary>
    public abstract class LinksDAO
    {
        #region Update Link废弃
        ///// <summary>
        ///// 更新一个链接对象
        ///// </summary>
        ///// <param name="link">要更新的对象</param>
        ///// <returns>是否更新成功</returns>
        //public static bool Update(Link link)
        //{
        //    //预制SQL语句模型;
        //    string sql = "UPDATE [Blog_Links] SET {0} WHERE (option_id=@Id)";
        //    List<string> list = new List<string>();
        //    List<SqlParameter> ps = new List<SqlParameter>();
        //    bool flag = false;
        //    //根据情况拼接SQL语句
        //    if (!string.IsNullOrEmpty(link.Name))
        //    {
        //        flag = true;
        //        list.Add("[link_name]=@Name");
        //        ps.Add(new SqlParameter("@Name", link.Name));
        //    }
        //    if (!string.IsNullOrEmpty(link.Url))
        //    {
        //        flag = true;
        //        list.Add("[link_url]=@Url");
        //        ps.Add(new SqlParameter("@Url", link.Url));
        //    }
        //    if (!string.IsNullOrEmpty(link.Title))
        //    {
        //        flag = true;
        //        list.Add("[link_title]=@Title");
        //        ps.Add(new SqlParameter("@Title", link.Title));
        //    }
        //    if (!string.IsNullOrEmpty(link.Target))
        //    {
        //        flag = true;
        //        list.Add("[link_target]=@Target");
        //        ps.Add(new SqlParameter("@Target", link.Target));
        //    }
        //    if (link.OwnerId != null)
        //    {
        //        flag = true;
        //        list.Add("[link_owner_id]=@Type");
        //        ps.Add(new SqlParameter("@Type", link.OwnerId));
        //    }
        //    if (link.Update != null)
        //    {
        //        flag = true;
        //        list.Add("[link_update]=@Update");
        //        ps.Add(new SqlParameter("@Update", link.Update));
        //    }
        //    if (link.Visible != null)
        //    {
        //        flag = true;
        //        list.Add("[link_visible]=@Visible");
        //        ps.Add(new SqlParameter("@Visible", link.Visible));
        //    }
        //    if (link.Order != null)
        //    {
        //        flag = true;
        //        list.Add("[link_order]=@Order");
        //        ps.Add(new SqlParameter("@Order", link.Order));
        //    }
        //    if (link.ParentId != null)
        //    {
        //        flag = true;
        //        list.Add("[link_parent_id]=@ParentId");
        //        ps.Add(new SqlParameter("@ParentId", link.ParentId));
        //    }
        //    if (flag)
        //    {
        //        //SQL语句有效
        //        sql = string.Format(sql, string.Join(",", list));
        //        ps.Add(new SqlParameter("@Id", link.Id));
        //        int res = SqlHelper.ExecuteNonQuery(sql, ps.ToArray());
        //        return res > 0 ? true : false;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}
        #endregion

        #region 增加链接
        /// <summary>
        /// 向链接表中插入一行数据, 返回受影响行数
        /// </summary>
        /// <param name="link">要插入的对象</param>
        /// <returns>返回受影响行数</returns>
        public static int Insert(Link link)
        {
            string sql = @"INSERT INTO [Blog_Links]
                                    (
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    )
                                   VALUES 
                                    (
                                        @Name,
                                        @Url,
                                        @Title,
                                        @Target,
                                        @Type,
                                        @OwnerId,
                                        @Update,
                                        @Visible,
                                        @Order,
                                        @ParentId
                                    )";
            return ExecuteNonQuery(link, sql);
        }
        #endregion

        #region 删除链接

        #region 删除一条数据
        /// <summary>
        /// 根据id删除对应的一条选项记录
        /// </summary>
        /// <param name="linkid">要删除记录的ID</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int linkid)
        {
            string sql = "DELETE FROM [Blog_Links] WHERE [link_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", linkid));
        }
        #endregion

        #region 删除多条数据
        /// <summary>
        /// 根据id数组删除对应的多条选项记录
        /// </summary>
        /// <param name="linkids">要删除记录的ID数组</param>
        /// <returns>受影响行数</returns>
        public static int Delete(int[] linkids)
        {
            string ids = string.Join(",", linkids);
            string sql = "DELETE FROM [Blog_Links] WHERE [link_id] in (" + ids + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }
        #endregion

        #endregion

        #region 更新链接
        /// <summary>
        /// 更新一个链接对象, 返回受影响行数
        /// </summary>
        /// <param name="link">要更新的对象</param>
        /// <returns>受影响行数</returns>
        public static int Update(Link link)
        {
            //预制SQL语句模型;
            string sql = @"UPDATE 
                                        [Blog_Links]
                                   SET
                                        [link_name]=@Name,
                                        [link_url]=@Url,
                                        [link_title]=@Title,
                                        [link_target]=@Target,
                                        [link_type]=@Type,
                                        [link_owner_id]=@OwnerId,
                                        [link_update]=@Update,
                                        [link_visible]=@Visible,
                                        [link_order]=@Order,
                                        [link_parent_id]=@ParentId
                                   WHERE
                                        [link_id]=@Id;";
            return ExecuteNonQuery(link, sql);
        }
        #endregion

        #region 选取链接

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("link_id", "Blog_Links");
        }
        #endregion

        #region 选取全部链接
        /// <summary>
        /// 取出当前链接表中的所有链接信息
        /// </summary>
        /// <returns>包涵所有链接信息的数据表</returns>
        public static List<Link> SelectLinks()
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links];";
            return Select(sql);
        }
        #endregion

        #region 根据链接类型选取链接不区分链接是否启用
        /// <summary>
        /// 根据链接类型选取链接信息不区分链接是否启用
        /// </summary>
        /// <param name="type">类型名称(nav;footer;blogroll...)</param>
        /// <returns>包涵指定类型所有链接信息的数据表</returns>
        public static List<Link> SelectLinks(string type)
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_type]=@Type
                                    ORDER BY
                                        [link_order];";
            return Select(sql, new SqlParameter("@Type", type.ToLower()));
        }
        #endregion

        #region 根据链接类型选取启用链接
        /// <summary>
        /// 根据链接类型选取已经启用链接信息
        /// </summary>
        /// <param name="type">类型名称(nav;footer;blogroll...)</param>
        /// <param name="visible">是否是可用链接</param>
        /// <returns>包涵指定类型已经启用所有链接信息的数据表</returns>
        public static List<Link> SelectLinks(string type, bool visible)
        {
            string sql;
            if (visible)
            {
                sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_type]=@Type
                                    AND
                                        [link_visible]=1
                                    ORDER BY
                                        [link_order];";
            }
            else
            {
                sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_type]=@Type
                                    AND
                                        [link_visible]=0
                                    ORDER BY
                                        [link_order];";
            }
            return Select(sql,
                new SqlParameter("@Type", type.ToLower()));
        }
        #endregion

        #region 根据父ID选取全部链接对象集合
        /// <summary>
        /// 根据父ID取出当前链接表中的全部链接对象集合
        /// </summary>
        /// <returns>链接对象集合</returns>
        public static List<Link> SelectLinks(int parentid)
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_parent_id]=@Id
                                    ORDER BY
                                        [link_order];";
            return Select(sql, new SqlParameter("@Id", parentid));
        }
        #endregion

        #region 根据父ID选取是否启用链接对象集合
        /// <summary>
        /// 根据父ID取出当前链接表中的是否启用链接对象集合
        /// </summary>
        /// <returns>链接对象集合</returns>
        public static List<Link> SelectLinks(int parentid, bool visible)
        {
             string sql;
            if (visible)
            {
                sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_parent_id]=@Id
                                    AND
                                        [link_visible]=1
                                    ORDER BY
                                        [link_order];";
            }
            else
            {
                sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_parent_id]=@Id
                                    AND
                                        [link_visible]=0
                                    ORDER BY
                                        [link_order];";
            }
            return Select(sql, new SqlParameter("@Id", parentid));
        }
        #endregion

        #region 根据ID选取链接对象
        /// <summary>
        /// 根据ID取出当前链接表中的链接对象
        /// </summary>
        /// <returns>链接对象</returns>
        public static Link SelectLink(int linkid)
        {
            string sql = @"SELECT 
                                        [link_id],
                                        [link_name],
                                        [link_url],
                                        [link_title],
                                        [link_target],
                                        [link_type],
                                        [link_owner_id],
                                        [link_update],
                                        [link_visible],
                                        [link_order],
                                        [link_parent_id]
                                    FROM
                                        [Blog_Links]
                                    WHERE
                                        [link_id]=@Id;";
            List<Link> list = Select(sql, new SqlParameter("@Id", linkid));
            return list.Count != 0 ? list[0] : null;
        }
        #endregion

        #endregion

        #region 私有方法

        #region select
        /// <summary>
        /// 私有选取数据方法
        /// </summary>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="parameter">参数</param>
        /// <returns>数据集合</returns>
        private static List<Link> Select(string sql, params SqlParameter[] parameter)
        {
            List<Link> list = new List<Link>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    Link link = new Link(
                        reader.GetInt32(0),
                        reader.GetString(1),
                        reader.GetString(2),
                        reader.IsDBNull(3) ? null : reader.GetString(3),
                        reader.GetString(4),
                        reader.GetString(5),
                        reader.GetInt32(6),
                        reader.GetDateTime(7).ToString(),
                        reader.GetBoolean(8),
                        reader.GetInt32(9),
                        reader.GetInt32(10));
                    list.Add(link);
                }
            }
            return list;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 私有执行方法执行写入数据库操作
        /// </summary>
        /// <param name="link">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>受影响行数</returns>
        private static int ExecuteNonQuery(Link link, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Name", link.Name),
                new SqlParameter("@Url", link.Url),
                new SqlParameter("@Title", string.IsNullOrEmpty(link.Title) ? DBNull.Value : (object)link.Title),
                new SqlParameter("@Target", string.IsNullOrEmpty(link.Target) ? "_blank" : link.Target),
                new SqlParameter("@Type", link.Type),
                new SqlParameter("@OwnerId", link.OwnerId),
                new SqlParameter("@Update", link.Update),
                new SqlParameter("@Visible", link.Visible),
                new SqlParameter("@Order", link.Order),
                new SqlParameter("@ParentId", link.ParentId),
                new SqlParameter("@Id", link.Id));
        }
        #endregion

        #endregion
    }
}
